package com.alex.goods.dao;

import com.alex.goods.entity.GoodsOrders;
import com.alex.goods.entity.InOutSum;
import com.alex.goods.entity.Orders;
import com.alex.goods.util.Blank;
import com.alex.goods.util.DBUtil;

import java.util.List;

public class OrdersDao {
	
	
	
	public List<GoodsOrders> selectOrdersList(String sql, Integer page,Integer limit) {
		
		return DBUtil.executeDQL(sql, GoodsOrders.class,page,limit);
	}
	public void addOrder(String orderNo, String goodsNo, String factory, String singlePrice, Integer num,
			String datetime, String unit, String dateio ,
			String personName, String tel, String status) {
		// TODO Auto-generated method stub	
		
		String []sql = {
				"insert into orders(order_no,factory,single_price,num,datetime,unit,dateio,person_name,tel,`status`) values("
				+ "?,?,?,?,?,?,?,?,?,?   )" ,
				"update goods set num = num + ? where goods_no = ?",
				"insert into goods_order(goods_no,order_no) values(?,?)"
		};
		
		DBUtil.excuteDMLs(sql, new Object[][]{
				{orderNo,factory,singlePrice,num,datetime,unit,dateio,personName,tel,status},
				{num,goodsNo},
				{goodsNo,orderNo}
		});
	}
	
	
	public Long getOrdersNum(String sql) {
		// String sql = "select  count(1) from orders";
		return DBUtil.executeCount(sql);
	}
	

	public Long getInOutNum(String sql) {
	/*	String sql =  " select count(1) from  "
				+ "  (  select sum(o.num)  from orders o ,  goods g  , goods_order go  "
				+ "   where o.order_no = go.order_no and g.goods_no = go.goods_no   "
				+ "  GROUP BY  g.goods_no, o.`status`   "
				+ "  )  as z  ";*/
		return  DBUtil.executeCount(sql);
	}
	
	public List<InOutSum> selectSumList(String sqlfrom, Integer page, Integer limit) {
		return DBUtil.executeDQL(sqlfrom, InOutSum.class, page,limit);
	}
	
	public List<InOutSum> selectSumByGoodsNo(String goodsNo,String start,String end,String status1,String status2) {
		String sqlfrom = "	select sum(o.num) sum  "
				+ "   from orders o ,  goods g  ,    "
				+ "   goods_order go  "
				+ "  where o.order_no = go.order_no and g.goods_no = go.goods_no and g.goods_no = ? ";
		String sqlto = " group by `status`  ";

		if (!Blank.isBlank(status1) ||!Blank.isBlank(status2)) {
            int flag = 0;
			sqlfrom += "  and  ( " ;
			if (!Blank.isBlank(status1)) {
				sqlfrom += " `status` = '"+status1 + "' ";
                flag = 1;
			}
			if (!Blank.isBlank(status2)) {
                if (flag == 1) {
                    sqlfrom += " or ";
                }
                sqlfrom += " `status` = '"+status2 + "' ";
                flag = 1;
            }
            sqlfrom += "   ) ";
        }
		if (!Blank.isBlank(start) || !Blank.isBlank(end)) {
			if (!Blank.isBlank(start)) {
				sqlfrom += "   and  date(o.`dateio`) >= date( '" + start + "' )  ";
			}
			if (!Blank.isBlank(end)) {
				sqlfrom += " and  date(o.`dateio`) <=  date( '" + end + "' )  ";
			}
		}
		sqlfrom +=  sqlto;
		System.out.println(sqlfrom);
		return DBUtil.executeDQL(sqlfrom, InOutSum.class,goodsNo);
	}

	public Integer getOrderNumByOrderNo(String orderNo) {
		String sql = "select * from orders where order_no = ?";
		List<Orders> list = DBUtil.executeDQL(sql, Orders.class,orderNo);
	    return list.get(0).getNum();
	}

	public void updateOrder(String goodsNo, String orderNo , String factory,
							String singlePrice, Integer num, String dateio, String unit,
							String personName, String tel, String statusx) {
		Integer finalNum = num - this.getOrderNumByOrderNo(orderNo);
		String []sql = {
			"   update orders set factory = ?, single_price = ? ,  num = ? , dateio= ? , "
			+  "  unit = ? , person_name = ?, tel = ? , `status` = ?   where order_no = ?  " ,
			" update goods set num = num +  ?  where goods_no = ? "
		};
        DBUtil.excuteDMLs(sql,new Object[][] {  {factory,singlePrice,num,dateio,unit,personName,tel,statusx,orderNo},
				{finalNum,goodsNo}  });

		/*
		String sql = "   update orders set factory = ? ,  "
				+ "	 single_price = ? , num = ? ,  dateio = ? ,   "
				+ "	 unit= ? , person_name = ? , tel = ? ,  `status` =   ?  "
				+ "	 where order_no =  ?    " ;
	    */
		//  DBUtil.excuteDML(sql, factory, singlePrice , num ,dateio,unit,personName,tel,statusx , orderNo);
	}
	
	public void deleteOrderByOrderNo(String orderNo, String goodsNo) {
		// TODO Auto-generated method stub


		Integer finalNum = this.getOrderNumByOrderNo(orderNo);
        System.out.println(finalNum);

		String []sql = {
			"update goods set num  = num - ? where goods_no = ?",	
			"delete  from orders where order_no = ?",
			"delete from goods_order where order_no = ? and goods_no = ?"
		};

		DBUtil.excuteDMLs(sql, new Object[][] {
			{finalNum,goodsNo} , {orderNo}, {orderNo,goodsNo}
		});
	}
}
